AD-DRM 패스워드 LDAP 연동
AD계정 패스워드 연동 전 반드시 AD SSO 설정이 완료되어 있어야 함
1. 개요
- 클라이언트에서 ID/PW 전송 시에 해당 인증을 DB 가 아닌 고객사 AD서버에서 인증
- AD 접속 정보는 사용자 커스텀 정책에서 읽어와서 동작
- AD인증 오류 발생 시에 2 차적으로 DB 인증으로 진행
- 별도 DB인증 제한 횟수를 추가하여 해당 횟수 초과 시에 DB인증 차단
- 단, AD서버 (LDAP서버) 접속이 되지 않는 경우는 LDAP 인증 실패 횟수 및 DB인증 차단하지 않음.
- MS-SQL / MARIADB / ORACLE 환경만 지원
2. 구성도
3. 개선 내용
(1) DRM Client 에서 DRM Server 로 ID/PW 전달하여 LDAP 인증 기능 구현
(2) SCI Server에서 LDAP 인증 사용자 및 접속 주소를 확인 → LDAP 서버로 ID/PW 전달 → 인증 수행
(3) TFTDB에 LDAP 인증 성공 후 사용자가 입력한 비밀번호를 업데이트 함
4. 패치 내용
(1) 플러그인
-
플러그인 경로 :
AKS : $SCI_Server/AKS/plugins
LMS : $SCI_Server/LMS/plugins
PMS : $SCI_Server/PMS/plugins
SCPDTSAgent : $SCI_Server/SPDTSAgent/plugins -
플러그 인 명
플러그인 | 패치 대상 | 비고 |
---|---|---|
aks.ds.uni.authenticate.gradeinfo35_2023.11.21.8.jar aks.ds.uni.registerpc30_2023.11.21.8.jar | AKS | AKS 단독 패치 |
dev.softcamp.swork.share.authPolicy.methods_2023.11.21.8.jar | AKS/LMS/PMS | 공통 패치 (SPDTSAgent 제외) |
dev.softcamp.share.objects_2023.7.13.7.jar dev.softcamp.swork.server_2023.7.12.7.jar | AKS/LMS/PMS/SPDTSAgent | 공통 패치 (모든 서비스) |
반드시 기존의 플러그인은 다른 백업 경로로 copy 후 프로시저 패치 진행
서비스가 aks.ds.uni.authenticate.gradeinfo35_* 형식으로 파일을 읽어와 동작이 되기 때문에 단순히 파일명을 변경하는 것으로는 플러그인이 무효화 되지 않음
*** 잘못된 예시 )
(2) 저장프로시저
- MSSQL
- MARIADB
- ORACLE
MSSQL 저장프로시저 확인
USE TFTDB
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COMM_CHECK_SSO_LDAPAUTH]') AND type in (N'P', N'PC'))
DROP PROCEDURE COMM_CHECK_SSO_LDAPAUTH;
GO
CREATE PROCEDURE [dbo].[COMM_CHECK_SSO_LDAPAUTH]
@USER_ID NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @POLICY_VALUE NVARCHAR(4000), @TOP_COUNT INT;
DECLARE @POLICY_ID NVARCHAR(260) = N'COMM_SSO_LDAPAUTH';
DECLARE @FAIL_POLICY_ID NVARCHAR(260) = N'LDAP_LOGINFAIL_NUM';
DECLARE @S_POS INT, @POS INT;
DECLARE @PW_UPDATE NVARCHAR(1)
, @CONNECT_TIMEOUT SMALLINT
, @LDAP_HOST NVARCHAR(260)
, @LDAP_PORT INT
, @LDAP_DOMAIN NVARCHAR(260)
, @LDAP_ID_FORMAT NVARCHAR(260)
, @LDAP_SSL NVARCHAR(1)
, @DB_CERTIFY_LIMIT SMALLINT
, @LDAP_LOGINFAIL_NUM SMALLINT;
/*
* 커스텀정책 저장 구조 개선 : TFS#136389
*/
WITH GROUP_PATH_INFO (TARGET_TYPE, TARGET_ID, PARENTGROUP_ID, DEPTH) AS
(
SELECT N'U' AS TARGET_TYPE, USER_ID AS TARGET_ID, GROUP_ID AS PARENTGROUP_ID, 0 AS DEPTH
FROM USERGROUP_INFO
WHERE USER_ID = @USER_ID
AND NODE_PROPERTY = N'1'
AND ISNULL(OTHERJOB_FLAG, N'0') = N'0'
UNION ALL
SELECT N'G', A.GROUP_ID, A.PARENTGROUP_ID, B.DEPTH + 1
FROM PARENTGROUP_INFO A INNER JOIN
GROUP_PATH_INFO B ON A.GROUP_ID = B.PARENTGROUP_ID
WHERE A.NODE_PROPERTY = N'1'
)
SELECT @POLICY_VALUE = T.POLICY_VALUE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY B.POLICY_ID ORDER BY A.DEPTH) AS RN
, B.POLICY_VALUE
, B.USE_FLAG
FROM GROUP_PATH_INFO A INNER JOIN
(SELECT * FROM CUSTOMPOLICYAUTH_INFO WHERE POLICY_ID = @POLICY_ID) B
ON (A.TARGET_TYPE = B.TARGET_TYPE AND A.TARGET_ID = B.TARGET_ID)
) AS T
WHERE T.RN = 1
AND T.USE_FLAG = 1
AND EXISTS (SELECT USE_FLAG FROM CUSTOMPOLICY_INFO WHERE POLICY_ID = @POLICY_ID AND USE_FLAG = 1)
OPTION (MAXRECURSION 20);
/*
PW_UPDATE=Y;CONNET_TIMEOUT=5;LDAP_HOST=softcamp.co.kr;LDAP_PORT=636;LDAP_DOMAIN=DC=softcamp,DC=co,DC=kr;LDAP_ID_FORMAT=SOFTCAMP\;LDAP_SSL=Y;
*/
IF (@POLICY_VALUE IS NOT NULL)
BEGIN
SET @TOP_COUNT = 1
END
ELSE BEGIN
SET @TOP_COUNT = 0
GOTO END_POS;
END
SET @S_POS = 1
-- PW_UPDATE
SET @S_POS = CHARINDEX(N'PW_UPDATE=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @PW_UPDATE = REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'PW_UPDATE=', N'')
END
-- CONNET_TIMEOUT
SET @S_POS = CHARINDEX(N'CONNET_TIMEOUT=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @CONNECT_TIMEOUT = CAST(REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'CONNET_TIMEOUT=', N'') AS SMALLINT)
END
-- CONNECT_TIMEOUT
SET @S_POS = CHARINDEX(N'CONNECT_TIMEOUT=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @CONNECT_TIMEOUT = CAST(REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'CONNECT_TIMEOUT=', N'') AS SMALLINT)
END
-- LDAP_HOST
SET @S_POS = CHARINDEX(N'LDAP_HOST=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @LDAP_HOST = REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'LDAP_HOST=', N'')
END
-- LDAP_PORT=
SET @S_POS = CHARINDEX(N'LDAP_PORT=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @LDAP_PORT = CAST(REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'LDAP_PORT=', N'') AS INT)
END
-- LDAP_DOMAIN=
SET @S_POS = CHARINDEX(N'LDAP_DOMAIN=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @LDAP_DOMAIN = REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'LDAP_DOMAIN=', N'')
END
-- LDAP_ID_FORMAT=
SET @S_POS = CHARINDEX(N'LDAP_ID_FORMAT=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @LDAP_ID_FORMAT = REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'LDAP_ID_FORMAT=', N'')
END
-- LDAP_SSL
SET @S_POS = CHARINDEX(N'LDAP_SSL=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @LDAP_SSL = REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'LDAP_SSL=', N'')
END
-- DB_CERTIFY_LIMIT=
SET @S_POS = CHARINDEX(N'DB_CERTIFY_LIMIT=', @POLICY_VALUE)
IF (@S_POS > 0)
BEGIN
SET @POS = CHARINDEX(N';', @POLICY_VALUE, @S_POS)
SET @DB_CERTIFY_LIMIT = CAST(REPLACE(SUBSTRING(@POLICY_VALUE, @S_POS, CASE WHEN @POS = 0 THEN LEN(@POLICY_VALUE) ELSE @POS-@S_POS END), N'DB_CERTIFY_LIMIT=', N'') AS SMALLINT)
END
-- LDAP_LOGINFAIL_NUM
SET @LDAP_LOGINFAIL_NUM =
(
SELECT CAST(SUBSTRING(POLICY_VALUE, 1, DATALENGTH(POLICY_VALUE)/2) AS SMALLINT)
FROM CUSTOMPOLICYAUTH_INFO
WHERE TARGET_TYPE = N'U'
AND TARGET_ID = @USER_ID
AND POLICY_ID = @FAIL_POLICY_ID
);
END_POS:
SELECT TOP (@TOP_COUNT)
@PW_UPDATE AS PW_UPDATE
, @CONNECT_TIMEOUT AS CONNECT_TIMEOUT
, @LDAP_HOST AS LDAP_HOST
, @LDAP_PORT AS LDAP_PORT
, @LDAP_DOMAIN AS LDAP_DOMAIN
, @LDAP_ID_FORMAT AS LDAP_ID_FORMAT
, @LDAP_SSL AS LDAP_SSL
, ISNULL(@DB_CERTIFY_LIMIT, 0) AS DB_CERTIFY_LIMIT
, ISNULL(@LDAP_LOGINFAIL_NUM, 0) AS LDAP_LOGINFAIL_NUM
;
END
GO
MARIADB 저장프로시저 확인
USE TFTDB;
DROP PROCEDURE IF EXISTS `COMM_CHECK_SSO_LDAPAUTH`;
DELIMITER //
CREATE PROCEDURE `COMM_CHECK_SSO_LDAPAUTH`(
IN `P_USER_ID` VARCHAR(128)
)
BEGIN
DECLARE `V_POLICY_ID` VARCHAR(128) DEFAULT 'COMM_SSO_LDAPAUTH';
DECLARE `V_FAIL_POLICY_ID` VARCHAR(128) DEFAULT 'LDAP_LOGINFAIL_NUM';
WITH RECURSIVE GROUP_PATH_INFO (TARGET_TYPE, TARGET_ID, PARENTGROUP_ID, DEPTH) AS
(
SELECT 'U' AS TARGET_TYPE, USER_ID AS TARGET_ID, GROUP_ID AS PARENTGROUP_ID, 0 AS DEPTH
FROM USERGROUP_INFO
WHERE USER_ID = P_USER_ID
AND NODE_PROPERTY = '1'
AND IFNULL(OTHERJOB_FLAG, '0') = '0'
UNION ALL
SELECT 'G', A.GROUP_ID, A.PARENTGROUP_ID, B.DEPTH + 1
FROM PARENTGROUP_INFO A INNER JOIN
GROUP_PATH_INFO B ON A.GROUP_ID = B.PARENTGROUP_ID
WHERE A.NODE_PROPERTY = '1'
AND B.DEPTH < 20
)
/*
PW_UPDATE=Y;CONNECT_TIMEOUT=5;LDAP_HOST=SCDC01.softcamp.co.kr;LDAP_PORT=389;LDAP_DOMAIN=DC=softcamp,DC=co,DC=kr;LDAP_ID_FORMAT=softcamp;LDAP_SSL=N;DB_CERTIFY_LIMIT=5;
*/
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 1), ';', -1), '=', -1) AS PW_UPDATE
, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 2), ';', -1), '=', -1) AS INT) AS CONNECT_TIMEOUT
, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 3), ';', -1), '=', -1) AS LDAP_HOST
, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 4), ';', -1), '=', -1) AS INT) AS LDAP_PORT
, SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 5), ';', -1),
LOCATE('=', SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 5), ';', -1))+1) AS LDAP_DOMAIN
, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 6), ';', -1), '=', -1) AS LDAP_ID_FORMAT
, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 7), ';', -1), '=', -1) AS LDAP_SSL
, IFNULL(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(POLICY_VALUE, ';', 8), ';', -1), '=', -1) AS INT), 0) AS DB_CERTIFY_LIMIT
, IFNULL((SELECT CAST(POLICY_VALUE AS INT) FROM CUSTOMPOLICYAUTH_INFO WHERE TARGET_TYPE = 'U' AND TARGET_ID = P_USER_ID AND POLICY_ID = V_FAIL_POLICY_ID), 0) AS LDAP_LOGINFAIL_NUM
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY B.POLICY_ID ORDER BY A.DEPTH) AS RN
, A.DEPTH
, B.POLICY_VALUE
FROM GROUP_PATH_INFO A INNER JOIN
CUSTOMPOLICYAUTH_INFO B ON (A.TARGET_TYPE = B.TARGET_TYPE AND A.TARGET_ID = B.TARGET_ID) INNER JOIN
CUSTOMPOLICY_INFO C ON (B.POLICY_ID = C.POLICY_ID)
WHERE B.POLICY_ID = V_POLICY_ID
AND B.USE_FLAG = 1
AND C.USE_FLAG = 1
) AS T
WHERE T.RN = 1
;
END//
DELIMITER ;
ORACLE 저장프로시저 확인
/*------------------------------------------------------------------------------
-- 개체 이름: COMM_CHECK_SSO_LDAPAUTH
-- 만든 날짜: 2023.06.02 오후 4:20:00
-- 수정한 날짜: 2023.11.15 오후 4:17:00
-- 상태: VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE PROCEDURE COMM_CHECK_SSO_LDAPAUTH
(
I_USER_ID IN USER_INFO.USER_ID%TYPE,
P_REFCUR OUT SYS_REFCURSOR
)
AS
V_POLICY_ID CUSTOMPOLICYAUTH_INFO.POLICY_ID%TYPE := 'COMM_SSO_LDAPAUTH';
V_FAIL_POLICY_ID CUSTOMPOLICYAUTH_INFO.POLICY_ID%TYPE := 'LDAP_LOGINFAIL_NUM';
BEGIN
OPEN P_REFCUR FOR
WITH GROUP_PATH_INFO (TARGET_TYPE, TARGET_ID, PARENTGROUP_ID, DEPTH) AS
(
SELECT 'U' AS TARGET_TYPE, USER_ID AS TARGET_ID, GROUP_ID AS PARENTGROUP_ID, 0 AS DEPTH
FROM USERGROUP_INFO
WHERE USER_ID = I_USER_ID
AND NODE_PROPERTY = '1'
AND NVL(OTHERJOB_FLAG, '0') = '0'
UNION ALL
SELECT 'G', A.GROUP_ID, A.PARENTGROUP_ID, B.DEPTH + 1
FROM PARENTGROUP_INFO A INNER JOIN
GROUP_PATH_INFO B ON A.GROUP_ID = B.PARENTGROUP_ID
WHERE A.NODE_PROPERTY = '1'
AND B.DEPTH < 20
)
SELECT
REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 1), 'PW_UPDATE=', '') AS PW_UPDATE
, CASE WHEN REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 2) LIKE 'CONNET_TIMEOUT=%' THEN REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 2), 'CONNET_TIMEOUT=', '')
ELSE REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 2), 'CONNECT_TIMEOUT=', '') END AS CONNECT_TIMEOUT
, REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 3), 'LDAP_HOST=', '') AS LDAP_HOST
, REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 4), 'LDAP_PORT=', '') AS LDAP_PORT
, REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 5), 'LDAP_DOMAIN=', '') AS LDAP_DOMAIN
, REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 6), 'LDAP_ID_FORMAT=', '') AS LDAP_ID_FORMAT
, REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 7), 'LDAP_SSL=', '') AS LDAP_SSL
, NVL(TO_NUMBER(REPLACE(REGEXP_SUBSTR(T.POLICY_VALUE, '[^;]+', 1, 8), 'DB_CERTIFY_LIMIT=', '')), 0) AS DB_CERTIFY_LIMIT
, NVL((SELECT TO_NUMBER(POLICY_VALUE) FROM CUSTOMPOLICYAUTH_INFO WHERE TARGET_TYPE = 'U' AND TARGET_ID = I_USER_ID AND POLICY_ID = V_FAIL_POLICY_ID), 0) AS LDAP_LOGINFAIL_NUM
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY B.POLICY_ID ORDER BY A.DEPTH) AS RN
, A.DEPTH
, B.POLICY_VALUE
FROM GROUP_PATH_INFO A INNER JOIN
CUSTOMPOLICYAUTH_INFO B ON (A.TARGET_TYPE = B.TARGET_TYPE AND A.TARGET_ID = B.TARGET_ID) INNER JOIN
CUSTOMPOLICY_INFO C ON (B.POLICY_ID = C.POLICY_ID)
WHERE B.POLICY_ID = V_POLICY_ID
AND B.USE_FLAG = 1
AND C.USE_FLAG = 1
) T
WHERE T.RN = 1
;
END COMM_CHECK_SSO_LDAPAUTH;
(3) AKS.lax
-
수정 내용 : 파일의 제일 하단에 내용 추가
경로 : $SCI_Server/AKS/AKS.lax…
…
# SWORK.SERVER.PORT
# -----------------
# Sets AKS Port
swork.server.port="62000"
use.ldap.authentication=true필수사항플러그인, 저장프로시저, AKS.lax 패치 후 AKS, LMS, PMS 서비스 재시작 필수
(4) 커스텀정책
-
커스텀정책 추가
항목 입력값 제품 타입 공통 ID COMM_SSO_LDAPAUTH 제목 SCI Server 사용자 ID/PW에 대한 LDAP 인증 타입 EDIT 사용 여부 사용 상세 설명 사용여부;연결만료시간;AD서버 IP;AD서버 포트(389or636);AD 도메인;AD 주소;ssl여부;인증제한 횟수
-
커스텀정책 등록
커스텀정책 값PW_UPDATE=Y;CONNECT_TIMEOUT=5;LDAP_HOST=10.10.222.75;LDAP_PORT=389;LDAP_DOMAIN=DC=ADSERVER,DC=com;LDAP_ID_FORMAT=ADSERVER;LDAP_SSL=N;DB_CERTIFY_LIMIT=5;
5. 결과 확인
(1) 정상 동작 확인
① PC 부팅 시 AD계정으로 로그인한다.
② AD SSO로 인해 로그인 된 DRM을 로그아웃한다.
③ 수동으로 재로그인을 진행하며 DRM 패스워드가 AD 패스워드와 동일하게 변경됐는지 확인한다.
(2) 패스워드 연동 실패 시
① Client Side 확인
- 사용자 PC에서 직접 AD서버와 통신하여 커스텀정책을 pass시키고 연동이 됐는지 확인(java 설치 필요)
- 조건 : java 설치 및 LDAPCertification.jar 파일이 필요함
- 명령어 : java -jar LDAPCertification.jar
- 조건 : java 설치 및 LDAPCertification.jar 파일이 필요함
② Server Side 확인
- 프로시저 확인
- DB에서 저장프로시저를 실행하여 저장프로시저 및 연동값이 정상적으로 들어갔는지 확인
- MSSQL
- MARIADB
EXEC TFTDB.dbo.COMM_CHECK_SSO_LDAPAUTH N'사용자ID';
CALL TFTDB.COMM_CHECK_SSO_LDAPAUTH ('사용자ID');
- DB에서 저장프로시저를 실행하여 저장프로시저 및 연동값이 정상적으로 들어갔는지 확인
- 계정 확인
- AD 서버에 실제로 존재하는 사용자인지 확인
- AD 계정 ID와 DRM 계정 ID가 일치하는 사용자인지 확인
- AKS 로그 확인
- AKS 로그 수집하여 CRM 티켓 접수